package zy.dao.sort.allot.impl;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import zy.dao.BaseDaoImpl;
import zy.dao.sort.allot.AllotDAO;
import zy.dto.common.BarcodeImportDto;
import zy.dto.common.ProductDto;
import zy.dto.shop.want.ProductStorePriceDto;
import zy.entity.base.product.T_Base_Product;
import zy.entity.base.product.T_Base_Product_Shop_Price;
import zy.entity.base.size.T_Base_Size;
import zy.entity.sort.allot.T_Sort_Allot;
import zy.entity.sort.allot.T_Sort_AllotList;
import zy.entity.stock.data.T_Stock_DataBill;
import zy.entity.sys.user.T_Sys_User;
import zy.util.CommonUtil;
import zy.util.DateUtil;
import zy.util.StringUtil;

@Repository
public class AllotDAOImpl extends BaseDaoImpl implements AllotDAO{

	private String getWhereSql(Map<String, Object> params){
		String fromJsp = StringUtil.trimString(params.get("fromJsp"));
		String at_type = StringUtil.trimString(params.get("at_type"));
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		StringBuffer sql = new StringBuffer();
		//审核状态，针对不同页面，作不同逻辑处理
		if("warn".equals(fromJsp)){
			if(StringUtil.isNotEmpty(params.get("at_ar_state"))){
				sql.append(" AND at_ar_state = :at_ar_state ");
				if(CommonUtil.WANT_AR_STATE_NOTAPPROVE.equals(params.get("at_ar_state")) && "0".equals(at_type)){
					if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
						sql.append(" AND at_applyamount = 0");
					}else {
						sql.append(" AND at_applyamount != 0");
					}
				}
			}else {
				if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
					if("0".equals(at_type)){
						sql.append(" AND (at_ar_state IN (1,5) OR (at_ar_state = 0 AND at_applyamount = 0))");
					}else {
						sql.append(" AND at_ar_state IN (0,3)");
					}
				}else {
					if("0".equals(at_type)){
						sql.append(" AND (at_ar_state IN (3) OR (at_ar_state = 0 AND at_applyamount != 0))");
					}else {
						sql.append(" AND at_ar_state IN (0,1,5)");
					}
				}
			}
		}else{
			if (StringUtil.isNotEmpty(params.get("at_ar_state"))) {
				sql.append(" AND at_ar_state = :at_ar_state ");
			}
		}
		if (StringUtil.isNotEmpty(params.get("at_isdraft"))) {
			sql.append(" AND at_isdraft = :at_isdraft ");
		}
		if (StringUtil.isNotEmpty(params.get("begindate"))) {
			sql.append(" AND at_sysdate >= :begindate ");
		}
		if (StringUtil.isNotEmpty(params.get("enddate"))) {
			sql.append(" AND at_sysdate <= :enddate ");
		}
		if (StringUtil.isNotEmpty(params.get("at_shop_code"))) {
			sql.append(" AND at_shop_code = :at_shop_code ");
		}
		if (StringUtil.isNotEmpty(params.get("at_outdp_code"))) {
			sql.append(" AND at_outdp_code = :at_outdp_code ");
		}
		if (StringUtil.isNotEmpty(params.get("at_indp_code"))) {
			sql.append(" AND at_indp_code = :at_indp_code ");
		}
		if (StringUtil.isNotEmpty(params.get("at_manager"))) {
			sql.append(" AND at_manager = :at_manager ");
		}
		if (StringUtil.isNotEmpty(params.get("at_number"))) {
			sql.append(" AND INSTR(at_number,:at_number) > 0 ");
		}
		sql.append(" AND at_type = :at_type ");
		sql.append(" AND t.companyid=:companyid");
		
		return sql.toString();
	}
	
	@Override
	public Map<String, Object> countSum(Map<String, Object> params) {
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		Integer allot_up = (Integer)params.get("allot_up");
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT COUNT(1) AS totalCount,");
		sql.append(" IFNULL(SUM(ABS(at_applyamount)),0) AS at_applyamount,");
		sql.append(" IFNULL(SUM(ABS(at_sendamount)),0) AS at_sendamount,");
		sql.append(" IFNULL(SUM(ABS(at_applymoney)),0) AS at_applymoney,");
		sql.append(" IFNULL(SUM(ABS(at_sendmoney)),0) AS at_sendmoney,");
		sql.append(" IFNULL(SUM(ABS(at_sendcostmoney)),0) AS at_sendcostmoney,");
		sql.append(" IFNULL(SUM(ABS(at_sendsellmoney)),0) AS at_sendsellmoney,");
		sql.append(" IFNULL(SUM(ABS(at_discount_money)),0) AS at_discount_money");
		sql.append(" FROM t_sort_allot t");
		sql.append(" JOIN t_base_shop sp ON sp_code = at_shop_code AND sp.companyid = t.companyid");
		if(CommonUtil.ONE.equals(shop_type)){//总公司：配货发货单、退货确认单
			sql.append(" WHERE 1 = 1");
			sql.append(" AND (sp_shop_type = "+CommonUtil.TWO);
			sql.append(" OR (sp_upcode = :shop_code AND sp_shop_type IN("+CommonUtil.FOUR+","+CommonUtil.FIVE+"))");
			sql.append(")");
		}else if(CommonUtil.TWO.equals(shop_type)){//分公司：配货发货单、退货确认单、配货申请单、退货申请单
			if(allot_up.intValue() == 0){//配货发货单、退货确认单
				sql.append(" WHERE 1 = 1");
				sql.append(" AND sp_upcode = :shop_code AND sp_shop_type IN("+CommonUtil.FOUR+","+CommonUtil.FIVE+")");
			}else {//配货申请单、退货申请单
				sql.append(" WHERE 1 = 1");
				sql.append(" AND sp_code = :shop_code");
			}
		}else if(CommonUtil.FOUR.equals(shop_type) || CommonUtil.FIVE.equals(shop_type)){//加盟店、合伙店：配货申请单、退货申请单
			sql.append(" WHERE 1 = 1");
			sql.append(" AND sp_code = :shop_code");
		}else {
			sql.append(" WHERE 1 = 2");
		}
		sql.append(getWhereSql(params));
		return namedParameterJdbcTemplate.queryForMap(sql.toString(), params);
	}

	@Override
	public List<T_Sort_Allot> list(Map<String, Object> params) {
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		Integer allot_up = (Integer)params.get("allot_up");
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT at_id,at_number,at_date,at_shop_code,at_outdp_code,at_indp_code,at_maker,at_manager,at_handnumber,at_applyamount,at_sendamount,");
		sql.append(" at_applymoney,at_sendmoney,at_sendcostmoney,at_sendsellmoney,at_discount_money,at_ba_code,at_property,at_remark,at_ar_state,at_ar_date,");
		sql.append(" at_pay_state,at_receivable,at_received,at_prepay,at_lastdebt,at_type,at_sysdate,at_us_id,at_isdraft,t.companyid,");
		sql.append(" sp_name AS shop_name,");
		sql.append(" (SELECT dp_name FROM t_base_depot dp WHERE dp_code = at_outdp_code AND dp.companyid = t.companyid LIMIT 1) AS outdepot_name,");
		sql.append(" (SELECT dp_name FROM t_base_depot dp WHERE dp_code = at_indp_code AND dp.companyid = t.companyid LIMIT 1) AS indepot_name");
		sql.append(" FROM t_sort_allot t");
		sql.append(" JOIN t_base_shop sp ON sp_code = at_shop_code AND sp.companyid = t.companyid");
		if(CommonUtil.ONE.equals(shop_type)){//总公司：配货发货单、退货确认单
			sql.append(" WHERE 1 = 1");
			sql.append(" AND (sp_shop_type = "+CommonUtil.TWO);
			sql.append(" OR (sp_upcode = :shop_code AND sp_shop_type IN("+CommonUtil.FOUR+","+CommonUtil.FIVE+"))");
			sql.append(")");
		}else if(CommonUtil.TWO.equals(shop_type)){//分公司：配货发货单、退货确认单、配货申请单、退货申请单
			if(allot_up.intValue() == 0){//配货发货单、退货确认单
				sql.append(" WHERE 1 = 1");
				sql.append(" AND sp_upcode = :shop_code AND sp_shop_type IN("+CommonUtil.FOUR+","+CommonUtil.FIVE+")");
			}else {//配货申请单、退货申请单
				sql.append(" WHERE 1 = 1");
				sql.append(" AND sp_code = :shop_code");
			}
		}else if(CommonUtil.FOUR.equals(shop_type) || CommonUtil.FIVE.equals(shop_type)){//加盟店、合伙店：配货申请单、退货申请单
			sql.append(" WHERE 1 = 1");
			sql.append(" AND sp_code = :shop_code");
		}else {
			sql.append(" WHERE 1 = 2");
		}
		sql.append(getWhereSql(params));
		if(StringUtil.isNotEmpty(params.get(CommonUtil.SIDX))){
			sql.append(" ORDER BY ").append(params.get(CommonUtil.SIDX)).append(" ").append(params.get(CommonUtil.SORD));
		}else {
			sql.append(" ORDER BY at_id DESC");
		}
		sql.append(" LIMIT :start,:end");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Sort_Allot.class));
	}
	
	@Override
	public T_Sort_Allot load(Integer at_id) {
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT at_id,at_number,at_date,at_shop_code,at_outdp_code,at_indp_code,at_maker,at_manager,at_handnumber,at_applyamount,at_sendamount,");
		sql.append(" at_applymoney,at_sendmoney,at_sendcostmoney,at_sendsellmoney,at_discount_money,at_ba_code,at_property,at_remark,at_ar_state,at_ar_date,");
		sql.append(" at_pay_state,at_receivable,at_received,at_prepay,at_lastdebt,at_type,at_sysdate,at_us_id,at_isdraft,t.companyid,");
		sql.append(" sp_name AS shop_name,");
		sql.append(" (SELECT dp_name FROM t_base_depot dp WHERE dp_code = at_outdp_code AND dp.companyid = t.companyid LIMIT 1) AS outdepot_name,");
		sql.append(" (SELECT dp_name FROM t_base_depot dp WHERE dp_code = at_indp_code AND dp.companyid = t.companyid LIMIT 1) AS indepot_name");
		sql.append(" FROM t_sort_allot t");
		sql.append(" JOIN t_base_shop sp ON sp_code = at_shop_code AND sp.companyid = t.companyid");
		sql.append(" WHERE at_id = :at_id");
		sql.append(" LIMIT 1");
		try {
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), new MapSqlParameterSource().addValue("at_id", at_id),
					new BeanPropertyRowMapper<>(T_Sort_Allot.class));
		} catch (Exception e) {
			return null;
		}
	}
	
	@Override
	public T_Sort_Allot load(String number,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT at_id,at_number,at_date,at_shop_code,at_outdp_code,at_indp_code,at_maker,at_manager,at_handnumber,at_applyamount,at_sendamount,");
		sql.append(" at_applymoney,at_sendmoney,at_sendcostmoney,at_sendsellmoney,at_discount_money,at_ba_code,at_property,at_remark,at_ar_state,at_ar_date,");
		sql.append(" at_pay_state,at_receivable,at_received,at_prepay,at_lastdebt,at_type,at_sysdate,at_us_id,at_isdraft,t.companyid,");
		sql.append(" sp_name AS shop_name,");
		sql.append(" (SELECT dp_name FROM t_base_depot dp WHERE dp_code = at_outdp_code AND dp.companyid = t.companyid LIMIT 1) AS outdepot_name,");
		sql.append(" (SELECT dp_name FROM t_base_depot dp WHERE dp_code = at_indp_code AND dp.companyid = t.companyid LIMIT 1) AS indepot_name");
		sql.append(" FROM t_sort_allot t");
		sql.append(" JOIN t_base_shop sp ON sp_code = at_shop_code AND sp.companyid = t.companyid");
		sql.append(" WHERE at_number = :at_number AND t.companyid = :companyid");
		sql.append(" LIMIT 1");
		try {
			return namedParameterJdbcTemplate.queryForObject(sql.toString(),
					new MapSqlParameterSource().addValue("at_number", number).addValue("companyid", companyid),
					new BeanPropertyRowMapper<>(T_Sort_Allot.class));
		} catch (Exception e) {
			return null;
		}
	}

	@Override
	public T_Sort_Allot check(String number,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT at_id,at_number,at_shop_code,at_outdp_code,at_indp_code,at_ar_state,at_pay_state,at_type,");
		sql.append(" at_applyamount,at_sendamount,at_receivable,at_discount_money,at_remark,at_us_id,companyid");
		sql.append(" FROM t_sort_allot t");
		sql.append(" WHERE at_number = :at_number");
		sql.append(" AND companyid = :companyid");
		sql.append(" LIMIT 1");
		try {
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), 
					new MapSqlParameterSource().addValue("at_number", number).addValue("companyid", companyid),
					new BeanPropertyRowMapper<>(T_Sort_Allot.class));
		} catch (Exception e) {
			return null;
		}
	}
	
	@Override
	public List<T_Sort_AllotList> detail_list_forcopy(List<Long> ids) {
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT atl_number,atl_pd_code,atl_sub_code,atl_sz_code,atl_szg_code,atl_cr_code,atl_br_code,");
		sql.append(" ABS(atl_applyamount) AS atl_applyamount,ABS(atl_sendamount) AS atl_sendamount,");
		sql.append(" atl_unitprice,atl_sellprice,atl_costprice,atl_remark,atl_type,t.companyid");
		sql.append(" FROM t_sort_allotlist t");
		sql.append(" WHERE atl_id IN(:ids)");
		return namedParameterJdbcTemplate.query(sql.toString(),new MapSqlParameterSource().addValue("ids", ids),
				new BeanPropertyRowMapper<>(T_Sort_AllotList.class));
	}
	
	@Override
	public List<T_Sort_AllotList> detail_list_forsavetemp(String at_number,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT atl_id,atl_number,atl_pd_code,atl_sub_code,atl_sz_code,atl_szg_code,atl_cr_code,atl_br_code,");
		sql.append(" ABS(atl_applyamount) AS atl_applyamount,ABS(atl_sendamount) AS atl_sendamount,");
		sql.append(" atl_unitprice,atl_sellprice,atl_costprice,atl_remark,atl_type,t.companyid");
		sql.append(" FROM t_sort_allotlist t");
		sql.append(" WHERE 1=1");
		sql.append(" AND atl_number = :atl_number");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" ORDER BY atl_id ASC");
		return namedParameterJdbcTemplate.query(sql.toString(),
				new MapSqlParameterSource().addValue("atl_number", at_number).addValue("companyid", companyid),
				new BeanPropertyRowMapper<>(T_Sort_AllotList.class));
	}
	
	@Override
	public List<T_Sort_AllotList> detail_list(Map<String, Object> params) {
		Object sidx = params.get(CommonUtil.SIDX);
		Object sord = params.get(CommonUtil.SORD);
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT atl_id,atl_number,atl_pd_code,atl_sub_code,atl_sz_code,atl_szg_code,atl_cr_code,atl_br_code,");
		sql.append(" ABS(atl_applyamount) AS atl_applyamount,ABS(atl_sendamount) AS atl_sendamount,");
		sql.append(" atl_unitprice,atl_sellprice,atl_costprice,atl_remark,atl_type,t.companyid,pd_no,pd_name,pd_unit, ");
		sql.append(" (SELECT cr_name FROM t_base_color cr WHERE cr_code = atl_cr_code AND cr.companyid = t.companyid LIMIT 1) AS cr_name,");
		sql.append(" (SELECT sz_name FROM t_base_size sz WHERE sz_code = atl_sz_code AND sz.companyid = t.companyid LIMIT 1) AS sz_name,");
		sql.append(" (SELECT br_name FROM t_base_bra br WHERE br_code = atl_br_code AND br.companyid = t.companyid LIMIT 1) AS br_name");
		sql.append(" FROM t_sort_allotlist t");
		sql.append(" JOIN t_base_product pd ON pd_code = t.atl_pd_code AND pd.companyid = t.companyid ");
		sql.append(" WHERE 1=1");
		sql.append(" AND atl_number = :atl_number");
		sql.append(" AND t.companyid = :companyid");
		if(StringUtil.isNotEmpty(sidx)){
			sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
		}else {
			sql.append(" ORDER BY atl_id DESC");
		}
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Sort_AllotList.class));
	}
	
	@Override
	public List<T_Sort_AllotList> detail_list_print(Map<String, Object> params) {
		Object sidx = params.get(CommonUtil.SIDX);
		Object sord = params.get(CommonUtil.SORD);
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT atl_id,atl_number,atl_pd_code,atl_sub_code,atl_sz_code,atl_szg_code,atl_cr_code,atl_br_code,");
		sql.append(" ABS(atl_applyamount) AS atl_applyamount,ABS(atl_sendamount) AS atl_sendamount,");
		sql.append(" atl_unitprice,atl_sellprice,atl_costprice,atl_remark,atl_type,t.companyid,pd_no,pd_name,pd_unit,pd_season,pd_year, ");
		sql.append(" (SELECT cr_name FROM t_base_color cr WHERE cr_code = atl_cr_code AND cr.companyid = t.companyid LIMIT 1) AS cr_name,");
		sql.append(" (SELECT sz_name FROM t_base_size sz WHERE sz_code = atl_sz_code AND sz.companyid = t.companyid LIMIT 1) AS sz_name,");
		sql.append(" (SELECT br_name FROM t_base_bra br WHERE br_code = atl_br_code AND br.companyid = t.companyid LIMIT 1) AS br_name,");
		sql.append(" (SELECT bd_name FROM t_base_brand bd WHERE bd_code = pd_bd_code AND bd.companyid = t.companyid LIMIT 1) AS bd_name,");
		sql.append(" (SELECT tp_name FROM t_base_type tp WHERE tp_code = pd_tp_code AND tp.companyid = t.companyid LIMIT 1) AS tp_name");
		sql.append(" FROM t_sort_allotlist t");
		sql.append(" JOIN t_base_product pd ON pd_code = t.atl_pd_code AND pd.companyid = t.companyid ");
		sql.append(" WHERE 1=1");
		sql.append(" AND atl_number = :atl_number");
		sql.append(" AND t.companyid = :companyid");
		if(StringUtil.isNotEmpty(sidx)){
			sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
		}else {
			sql.append(" ORDER BY atl_id DESC");
		}
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Sort_AllotList.class));
	}
	
	@Override
	public List<T_Sort_AllotList> detail_sum(Map<String, Object> params) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT atl_id,atl_pd_code,atl_szg_code,SUM(ABS(atl_applyamount)) AS atl_applyamount,SUM(ABS(atl_sendamount)) AS atl_sendamount,");
		sql.append(" atl_unitprice,atl_sellprice,atl_costprice,atl_remark,atl_type,t.companyid,pd_no,pd_name,pd_unit,");
		sql.append(" (SELECT bd_name FROM t_base_brand bd WHERE bd_code = pd_bd_code AND bd.companyid = t.companyid LIMIT 1) AS bd_name,");
		sql.append(" (SELECT tp_name FROM t_base_type tp WHERE tp_code = pd_tp_code AND tp.companyid = t.companyid LIMIT 1) AS tp_name");
		sql.append(" FROM t_sort_allotlist t ");
		sql.append(" JOIN t_base_product pd ON pd_code = t.atl_pd_code AND pd.companyid = t.companyid");  
		sql.append(" WHERE 1=1 ");
		sql.append(" AND atl_number = :atl_number");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" GROUP BY atl_pd_code");
		sql.append(" ORDER BY atl_pd_code ASC");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Sort_AllotList.class));
	}
	
	@Override
	public List<String> detail_szgcode(Map<String, Object> params) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT DISTINCT atl_szg_code");
		sql.append(" FROM t_sort_allotlist t ");
		sql.append(" WHERE 1=1 ");
		sql.append(" AND atl_number = :atl_number");
		sql.append(" AND t.companyid = :companyid");
		return namedParameterJdbcTemplate.queryForList(sql.toString(), params, String.class);
	}
	
	@Override
	public List<T_Sort_AllotList> temp_list_forimport(Integer at_type,Integer atl_up,Integer us_id,Integer companyid) {//只查询商品
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT atl_id,atl_pd_code,atl_sub_code,atl_sz_code,atl_szg_code,atl_cr_code,atl_br_code,atl_applyamount,atl_sendamount,");
		sql.append(" atl_unitprice,atl_sellprice,atl_costprice,atl_remark,atl_us_id,atl_type,t.companyid");
		sql.append(" FROM t_sort_allotlist_temp t");
		sql.append(" WHERE 1=1");
		sql.append(" AND atl_type = :atl_type");
		sql.append(" AND atl_up = :atl_up");
		sql.append(" AND atl_us_id = :atl_us_id");
		sql.append(" AND t.companyid = :companyid");
		return namedParameterJdbcTemplate.query(sql.toString(),
				new MapSqlParameterSource().addValue("atl_type", at_type).addValue("atl_up", atl_up).addValue("atl_us_id", us_id).addValue("companyid", companyid),
				new BeanPropertyRowMapper<>(T_Sort_AllotList.class));
	}
	
	@Override
	public List<T_Sort_AllotList> temp_list_forsave(Integer at_type,Integer atl_up,Integer us_id,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT atl_id,atl_pd_code,atl_sub_code,atl_sz_code,atl_szg_code,atl_cr_code,atl_br_code,atl_applyamount,atl_sendamount,");
		sql.append(" atl_unitprice,atl_sellprice,atl_costprice,atl_remark,atl_us_id,atl_type,t.companyid");
		sql.append(" FROM t_sort_allotlist_temp t");
		sql.append(" WHERE 1=1");
		sql.append(" AND atl_type = :atl_type");
		sql.append(" AND atl_up = :atl_up");
		sql.append(" AND atl_us_id = :atl_us_id");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" ORDER BY atl_id ASC");
		return namedParameterJdbcTemplate.query(sql.toString(),
				new MapSqlParameterSource().addValue("atl_type", at_type).addValue("atl_up", atl_up).addValue("atl_us_id", us_id).addValue("companyid", companyid),
				new BeanPropertyRowMapper<>(T_Sort_AllotList.class));
	}
	
	@Override
	public List<T_Sort_AllotList> temp_list(Map<String, Object> params) {
		Object sidx = params.get(CommonUtil.SIDX);
		Object sord = params.get(CommonUtil.SORD);
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT atl_id,atl_pd_code,atl_sub_code,atl_sz_code,atl_szg_code,atl_cr_code,atl_br_code,atl_applyamount,atl_sendamount,");
		sql.append(" atl_unitprice,atl_sellprice,atl_costprice,atl_remark,atl_us_id,atl_type,t.companyid,pd_no,pd_name,pd_unit, ");
		sql.append(" (SELECT cr_name FROM t_base_color cr WHERE cr_code = atl_cr_code AND cr.companyid = t.companyid LIMIT 1) AS cr_name,");
		sql.append(" (SELECT sz_name FROM t_base_size sz WHERE sz_code = atl_sz_code AND sz.companyid = t.companyid LIMIT 1) AS sz_name,");
		sql.append(" (SELECT br_name FROM t_base_bra br WHERE br_code = atl_br_code AND br.companyid = t.companyid LIMIT 1) AS br_name");
		sql.append(" FROM t_sort_allotlist_temp t");
		sql.append(" JOIN t_base_product pd ON pd_code = t.atl_pd_code AND pd.companyid = t.companyid ");
		sql.append(" WHERE 1=1");
		sql.append(" AND atl_type = :atl_type");
		sql.append(" AND atl_up = :atl_up");
		sql.append(" AND atl_us_id = :atl_us_id");
		sql.append(" AND t.companyid = :companyid");
		if(StringUtil.isNotEmpty(sidx)){
			sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
		}else {
			sql.append(" ORDER BY atl_id DESC");
		}
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Sort_AllotList.class));
	}

	@Override
	public List<T_Sort_AllotList> temp_sum(Integer at_type,Integer atl_up, Integer us_id,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT atl_id,atl_pd_code,atl_szg_code,SUM(atl_applyamount) AS atl_applyamount,SUM(atl_sendamount) AS atl_sendamount,");
		sql.append(" atl_unitprice,atl_sellprice,atl_costprice,atl_remark,atl_us_id,atl_type,t.companyid,pd_no,pd_name,pd_unit,");
		sql.append(" (SELECT bd_name FROM t_base_brand bd WHERE bd_code = pd_bd_code AND bd.companyid = t.companyid LIMIT 1) AS bd_name,");
		sql.append(" (SELECT tp_name FROM t_base_type tp WHERE tp_code = pd_tp_code AND tp.companyid = t.companyid LIMIT 1) AS tp_name");
		sql.append(" FROM t_sort_allotlist_temp t ");
		sql.append(" JOIN t_base_product pd ON pd_code = t.atl_pd_code AND pd.companyid = t.companyid");  
		sql.append(" WHERE 1=1 ");
		sql.append(" AND atl_type = :atl_type");
		sql.append(" AND atl_up = :atl_up");
		sql.append(" AND atl_us_id = :atl_us_id");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" GROUP BY atl_pd_code");
		sql.append(" ORDER BY atl_pd_code ASC");
		return namedParameterJdbcTemplate.query(sql.toString(), 
				new MapSqlParameterSource().addValue("atl_type", at_type).addValue("atl_up", atl_up).addValue("atl_us_id", us_id).addValue("companyid", companyid), 
				new BeanPropertyRowMapper<>(T_Sort_AllotList.class));
	}

	@Override
	public List<String> temp_szgcode(Map<String, Object> params) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT DISTINCT atl_szg_code");
		sql.append(" FROM t_sort_allotlist_temp t ");
		sql.append(" WHERE 1=1 ");
		sql.append(" AND atl_type = :atl_type");
		sql.append(" AND atl_up = :atl_up");
		sql.append(" AND atl_us_id = :atl_us_id");
		sql.append(" AND t.companyid = :companyid");
		return namedParameterJdbcTemplate.queryForList(sql.toString(), params, String.class);
	}
	
	@Override
	public Integer count_product(Map<String, Object> param) {
		Object searchContent = param.get("searchContent");
		Object alreadyExist = param.get("alreadyExist");
		Object exactQuery = param.get("exactQuery");
		Object at_number = param.get("at_number");
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT count(1)");
		sql.append(" FROM (");
		sql.append(" SELECT 1 FROM t_base_product t");
		if(StringUtil.isNotEmpty(at_number)){
			sql.append(" LEFT JOIN t_sort_allotlist atl ON atl_pd_code = pd_code AND atl.companyid = t.companyid AND atl_number = :at_number");
		}else {
			sql.append(" LEFT JOIN t_sort_allotlist_temp atl ON atl_pd_code = pd_code AND atl.companyid = t.companyid AND atl_type = :at_type AND atl_up = :atl_up AND atl_us_id = :us_id");
		}
		sql.append(" where 1 = 1");
		if(StringUtil.isNotEmpty(searchContent)){
			if("1".equals(exactQuery)){
				sql.append(" AND (t.pd_name = :searchContent OR t.pd_no = :searchContent)");
			}else {
				sql.append(" AND (instr(t.pd_name,:searchContent)>0 OR instr(t.pd_spell,:searchContent)>0 OR instr(t.pd_no,:searchContent)>0)");
			}
        }
		if("1".equals(alreadyExist)){
			sql.append(" AND atl_id IS NOT NULL ");
		}
		sql.append(" and t.companyid=:companyid");
		sql.append(" GROUP BY pd_code)t");
		return namedParameterJdbcTemplate.queryForObject(sql.toString(), param, Integer.class);
	}

	@Override
	public List<T_Base_Product> list_product(Map<String, Object> param) {
		Object sidx = param.get(CommonUtil.SIDX);
		Object sord = param.get(CommonUtil.SORD);
		Object searchContent = param.get("searchContent");
		Object alreadyExist = param.get("alreadyExist");
		Object exactQuery = param.get("exactQuery");
		Object at_number = param.get("at_number");
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT pd_id,pd_code,pd_no,pd_name,IF(atl_id IS NULL,0,1) AS exist");
		sql.append(" FROM t_base_product t");
		if(StringUtil.isNotEmpty(at_number)){
			sql.append(" LEFT JOIN t_sort_allotlist atl ON atl_pd_code = pd_code AND atl.companyid = t.companyid AND atl_number = :at_number");
		}else {
			sql.append(" LEFT JOIN t_sort_allotlist_temp atl ON atl_pd_code = pd_code AND atl.companyid = t.companyid AND atl_type = :at_type AND atl_up = :atl_up AND atl_us_id = :us_id");
		}
		sql.append(" where 1 = 1");
		if(StringUtil.isNotEmpty(searchContent)){
			if("1".equals(exactQuery)){
				sql.append(" AND (t.pd_name = :searchContent OR t.pd_no = :searchContent)");
			}else {
				sql.append(" AND (instr(t.pd_name,:searchContent)>0 OR instr(t.pd_spell,:searchContent)>0 OR instr(t.pd_no,:searchContent)>0)");
			}
        }
		if("1".equals(alreadyExist)){
			sql.append(" AND atl_id IS NOT NULL ");
		}
		sql.append(" and t.companyid=:companyid");
		sql.append(" GROUP BY pd_code");
		if(StringUtil.isNotEmpty(sidx)){
			sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
		}else {
			sql.append(" ORDER BY pd_id DESC");
		}
		sql.append(" LIMIT :start,:end");
		return namedParameterJdbcTemplate.query(sql.toString(), param, new BeanPropertyRowMapper<>(T_Base_Product.class));
	}
	
	@Override
	public T_Base_Product load_product(String pd_code,String sp_code,Integer companyid){
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT pd.pd_id,pd.pd_code,pd_no,pd_name,pd_szg_code,pd_unit,pd_year,pd_season,");
		sql.append(" f_GetProductSortPrice(pd.pd_code,:sp_code,pd.companyid) AS pd_sort_price,");
		sql.append(" f_GetProductSellPrice(pd.pd_code,:sp_code,pd.companyid) AS pd_sell_price,pd_cost_price,");
		sql.append(" (SELECT bd_name FROM t_base_brand bd WHERE bd_code = pd_bd_code AND bd.companyid = pd.companyid LIMIT 1) AS pd_bd_name,");
		sql.append(" (SELECT tp_name FROM t_base_type tp WHERE tp_code = pd_tp_code AND tp.companyid = pd.companyid LIMIT 1) AS pd_tp_name");
		sql.append(" ,(SELECT pdm_img_path FROM t_base_product_img pdm WHERE pdm_pd_code = pd.pd_code AND pdm.companyid = pd.companyid LIMIT 1) AS pdm_img_path");
		sql.append(" FROM t_base_product pd");
		sql.append(" WHERE 1=1");
		sql.append(" AND pd.pd_code = :pd_code");
		sql.append(" AND pd.companyid = :companyid");
		try{
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), 
					new MapSqlParameterSource().addValue("pd_code", pd_code).addValue("sp_code", sp_code).addValue("companyid", companyid),
					new BeanPropertyRowMapper<>(T_Base_Product.class));
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}
	}
	
	@Override
	public Map<String, Object> load_product_size(Map<String,Object> params) {
		Map<String, Object> resultMap = new HashMap<String, Object>();
		//1.查询尺码信息
		List<T_Base_Size> sizes = namedParameterJdbcTemplate.query(getSizeSQL(), params, new BeanPropertyRowMapper<>(T_Base_Size.class));
		resultMap.put("sizes",sizes);
		//2.获取颜色杯型信息
		List<ProductDto> inputs = namedParameterJdbcTemplate.query(getColorBraSQL(), params, new BeanPropertyRowMapper<>(ProductDto.class));
		resultMap.put("inputs",inputs);
		//3.已录入数量
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT atl_sz_code AS sz_code,atl_cr_code AS cr_code,atl_br_code AS br_code,atl_applyamount AS amount");
		sql.append(" FROM t_sort_allotlist_temp");
		sql.append(" WHERE 1=1");
		sql.append(" AND atl_pd_code = :pd_code");
		sql.append(" AND atl_us_id = :us_id");
		sql.append(" AND atl_type = :at_type");
		sql.append(" AND atl_up = :atl_up");
		sql.append(" AND companyid = :companyid");
		List<ProductDto> temps = namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(ProductDto.class));
		resultMap.put("temps",temps);
		//4.库存数量
		List<ProductDto> stocks = namedParameterJdbcTemplate.query(getStockSQL(), params, new BeanPropertyRowMapper<>(ProductDto.class));
		resultMap.put("stocks",stocks);
		return resultMap;
	}
	
	@Override
	public Map<String, Object> load_product_size_send(Map<String,Object> params) {
		Map<String, Object> resultMap = new HashMap<String, Object>();
		//1.查询尺码信息
		List<T_Base_Size> sizes = namedParameterJdbcTemplate.query(getSizeSQL(), params, new BeanPropertyRowMapper<>(T_Base_Size.class));
		resultMap.put("sizes",sizes);
		//2.获取颜色杯型信息
		List<ProductDto> inputs = namedParameterJdbcTemplate.query(getColorBraSQL(), params, new BeanPropertyRowMapper<>(ProductDto.class));
		resultMap.put("inputs",inputs);
		//3.已录入数量
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT atl_sz_code AS sz_code,atl_cr_code AS cr_code,atl_br_code AS br_code,atl_sendamount AS amount");
		sql.append(" FROM t_sort_allotlist");
		sql.append(" WHERE 1=1");
		sql.append(" AND atl_pd_code = :pd_code");
		sql.append(" AND atl_number = :at_number");
		sql.append(" AND companyid = :companyid");
		List<ProductDto> temps = namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(ProductDto.class));
		resultMap.put("temps",temps);
		//4.库存数量
		List<ProductDto> stocks = namedParameterJdbcTemplate.query(getStockSQL(), params, new BeanPropertyRowMapper<>(ProductDto.class));
		resultMap.put("stocks",stocks);
		return resultMap;
	}
	
	@Override
	public T_Sort_AllotList temp_queryUnitPrice(String pd_code, Integer at_type,Integer atl_up, Integer us_id, Integer companyid){
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT atl_unitprice");
		sql.append(" FROM t_sort_allotlist_temp");
		sql.append(" WHERE 1=1");
		sql.append(" AND atl_pd_code = :atl_pd_code");
		sql.append(" AND atl_type = :atl_type");
		sql.append(" AND atl_up = :atl_up");
		sql.append(" AND atl_us_id = :atl_us_id");
		sql.append(" AND companyid = :companyid");
		sql.append(" LIMIT 1");
		try {
			return namedParameterJdbcTemplate.queryForObject(sql.toString(),
					new MapSqlParameterSource().addValue("atl_pd_code", pd_code)
							.addValue("atl_type", at_type)
							.addValue("atl_up", atl_up)
							.addValue("atl_us_id", us_id)
							.addValue("companyid", companyid), 
					new BeanPropertyRowMapper<>(T_Sort_AllotList.class));
		} catch (Exception e) {
			return null;
		}
	}
	
	@Override
	public T_Sort_AllotList detail_queryUnitPrice(String pd_code, String at_number, Integer companyid){
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT atl_unitprice");
		sql.append(" FROM t_sort_allotlist");
		sql.append(" WHERE 1=1");
		sql.append(" AND atl_pd_code = :atl_pd_code");
		sql.append(" AND atl_number = :atl_number");
		sql.append(" AND companyid = :companyid");
		sql.append(" LIMIT 1");
		try {
			return namedParameterJdbcTemplate.queryForObject(sql.toString(),
					new MapSqlParameterSource().addValue("atl_pd_code", pd_code).addValue("atl_number", at_number).addValue("companyid", companyid), 
					new BeanPropertyRowMapper<>(T_Sort_AllotList.class));
		} catch (Exception e) {
			return null;
		}
	}
	
	@Override
	public T_Sort_AllotList temp_loadBySubCode(String sub_code,Integer at_type,Integer atl_up, Integer us_id, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT atl_id,atl_pd_code,atl_sub_code,atl_sz_code,atl_szg_code,atl_cr_code,atl_br_code,atl_applyamount,atl_sendamount,");
		sql.append(" atl_unitprice,atl_sellprice,atl_costprice,atl_remark,atl_us_id,atl_type,t.companyid ");
		sql.append(" FROM t_sort_allotlist_temp t");
		sql.append(" WHERE 1=1");
		sql.append(" AND atl_sub_code = :atl_sub_code");
		sql.append(" AND atl_type = :atl_type");
		sql.append(" AND atl_up = :atl_up");
		sql.append(" AND atl_us_id = :atl_us_id");
		sql.append(" AND companyid = :companyid");
		sql.append(" LIMIT 1");
		try{
			return namedParameterJdbcTemplate.queryForObject(sql.toString(),
					new MapSqlParameterSource()
							.addValue("atl_sub_code", sub_code)
							.addValue("atl_type", at_type)
							.addValue("atl_up", atl_up)
							.addValue("atl_us_id", us_id)
							.addValue("companyid", companyid),
					new BeanPropertyRowMapper<>(T_Sort_AllotList.class));
		}catch(Exception e){
			return null;
		}
	}
	
	@Override
	public T_Sort_AllotList detail_loadBySubCode(String sub_code,String at_number, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT atl_id,atl_number,atl_pd_code,atl_sub_code,atl_sz_code,atl_szg_code,atl_cr_code,atl_br_code,atl_applyamount,atl_sendamount,");
		sql.append(" atl_unitprice,atl_sellprice,atl_costprice,atl_remark,atl_type,t.companyid ");
		sql.append(" FROM t_sort_allotlist t");
		sql.append(" WHERE 1=1");
		sql.append(" AND atl_sub_code = :atl_sub_code");
		sql.append(" AND atl_number = :atl_number");
		sql.append(" AND companyid = :companyid");
		sql.append(" LIMIT 1");
		try{
			return namedParameterJdbcTemplate.queryForObject(sql.toString(),
					new MapSqlParameterSource()
							.addValue("atl_sub_code", sub_code)
							.addValue("atl_number", at_number)
							.addValue("companyid", companyid),
					new BeanPropertyRowMapper<>(T_Sort_AllotList.class));
		}catch(Exception e){
			return null;
		}
	}
	
	@Override
	public void temp_save(List<T_Sort_AllotList> temps) {
		StringBuffer sql = new StringBuffer("");
		sql.append("INSERT INTO t_sort_allotlist_temp");
		sql.append(" (atl_pd_code,atl_sub_code,atl_szg_code,atl_sz_code,atl_cr_code,atl_br_code,atl_applyamount,atl_sendamount,");
		sql.append(" atl_unitprice,atl_sellprice,atl_costprice,atl_remark,atl_us_id,atl_type,atl_up,companyid)");
		sql.append(" VALUES");
		sql.append(" (:atl_pd_code,:atl_sub_code,:atl_szg_code,:atl_sz_code,:atl_cr_code,:atl_br_code,:atl_applyamount,:atl_sendamount,");
		sql.append(" :atl_unitprice,:atl_sellprice,:atl_costprice,:atl_remark,:atl_us_id,:atl_type,:atl_up,:companyid)");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(temps.toArray()));
	}
	
	@Override
	public void temp_save(T_Sort_AllotList temp) {
		StringBuffer sql = new StringBuffer("");
		sql.append("INSERT INTO t_sort_allotlist_temp");
		sql.append(" (atl_pd_code,atl_sub_code,atl_szg_code,atl_sz_code,atl_cr_code,atl_br_code,atl_applyamount,atl_sendamount,");
		sql.append(" atl_unitprice,atl_sellprice,atl_costprice,atl_remark,atl_us_id,atl_type,atl_up,companyid)");
		sql.append(" VALUES");
		sql.append(" (:atl_pd_code,:atl_sub_code,:atl_szg_code,:atl_sz_code,:atl_cr_code,:atl_br_code,:atl_applyamount,:atl_sendamount,");
		sql.append(" :atl_unitprice,:atl_sellprice,:atl_costprice,:atl_remark,:atl_us_id,:atl_type,:atl_up,:companyid)");
		KeyHolder holder = new GeneratedKeyHolder(); 
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(temp),holder);
		temp.setAtl_id(holder.getKey().longValue());
	}
	
	@Override
	public void temp_update(List<T_Sort_AllotList> temps) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_sort_allotlist_temp");
		sql.append(" SET atl_applyamount = :atl_applyamount");
		sql.append(" WHERE 1=1");
		sql.append(" AND atl_sub_code = :atl_sub_code");
		sql.append(" AND atl_type = :atl_type");
		sql.append(" AND atl_up = :atl_up");
		sql.append(" AND atl_us_id = :atl_us_id");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(temps.toArray()));
	}
	
	@Override
	public void temp_updateById(List<T_Sort_AllotList> temps) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_sort_allotlist_temp");
		sql.append(" SET atl_applyamount = :atl_applyamount");
		sql.append(" WHERE 1=1");
		sql.append(" AND atl_id = :atl_id");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(temps.toArray()));
	}
	
	@Override
	public void temp_update(T_Sort_AllotList temp) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_sort_allotlist_temp");
		sql.append(" SET atl_applyamount = :atl_applyamount");
		sql.append(" WHERE 1=1");
		sql.append(" AND atl_id = :atl_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(temp));
	}
	
	@Override
	public void temp_updateprice(String pd_code, Double unitPrice, Integer at_type,Integer atl_up, Integer us_id, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_sort_allotlist_temp");
		sql.append(" SET atl_unitprice = :atl_unitprice");
		sql.append(" WHERE 1=1");
		sql.append(" AND atl_pd_code = :atl_pd_code");
		sql.append(" AND atl_type = :atl_type");
		sql.append(" AND atl_up = :atl_up");
		sql.append(" AND atl_us_id = :atl_us_id");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(),
				new MapSqlParameterSource()
						.addValue("atl_unitprice", unitPrice)
						.addValue("atl_pd_code", pd_code)
						.addValue("atl_type", at_type)
						.addValue("atl_up", atl_up)
						.addValue("atl_us_id", us_id)
						.addValue("companyid", companyid));
	}
	
	@Override
	public void temp_updateRemarkById(T_Sort_AllotList temp) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_sort_allotlist_temp");
		sql.append(" SET atl_remark = :atl_remark");
		sql.append(" WHERE 1=1");
		sql.append(" AND atl_id = :atl_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(temp));
	}
	
	@Override
	public void temp_updateRemarkByPdCode(T_Sort_AllotList temp) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_sort_allotlist_temp");
		sql.append(" SET atl_remark = :atl_remark");
		sql.append(" WHERE 1=1");
		sql.append(" AND atl_pd_code = :atl_pd_code");
		sql.append(" AND atl_type = :atl_type");
		sql.append(" AND atl_up = :atl_up");
		sql.append(" AND atl_us_id = :atl_us_id");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(temp));
	}
	
	@Override
	public void temp_del(List<T_Sort_AllotList> temps) {
		StringBuffer sql = new StringBuffer("");
		sql.append("DELETE FROM t_sort_allotlist_temp");
		sql.append(" WHERE 1=1");
		sql.append(" AND atl_sub_code = :atl_sub_code");
		sql.append(" AND atl_type = :atl_type");
		sql.append(" AND atl_up = :atl_up");
		sql.append(" AND atl_us_id = :atl_us_id");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(temps.toArray()));
	}
	
	@Override
	public void temp_del(Integer atl_id) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" DELETE FROM t_sort_allotlist_temp");
		sql.append(" WHERE atl_id=:atl_id");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("atl_id", atl_id));
	}
	
	@Override
	public void temp_delByPiCode(T_Sort_AllotList temp) {
		StringBuffer sql = new StringBuffer("");
		sql.append("DELETE FROM t_sort_allotlist_temp");
		sql.append(" WHERE 1=1");
		sql.append(" AND atl_pd_code = :atl_pd_code");
		if(StringUtil.isNotEmpty(temp.getAtl_cr_code())){
			sql.append(" AND atl_cr_code = :atl_cr_code");
		}
		if(StringUtil.isNotEmpty(temp.getAtl_br_code())){
			sql.append(" AND atl_br_code = :atl_br_code");
		}
		sql.append(" AND atl_type = :atl_type");
		sql.append(" AND atl_up = :atl_up");
		sql.append(" AND atl_us_id = :atl_us_id");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(temp));
	}
	
	@Override
	public void temp_clear(Integer at_type,Integer atl_up,Integer us_id,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append("DELETE FROM t_sort_allotlist_temp");
		sql.append(" WHERE 1=1");
		sql.append(" AND atl_type = :atl_type");
		sql.append(" AND atl_up = :atl_up");
		sql.append(" AND atl_us_id = :atl_us_id");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(),
				new MapSqlParameterSource().addValue("atl_type", at_type)
						.addValue("atl_up", atl_up)
						.addValue("atl_us_id", us_id)
						.addValue("companyid", companyid));
	}
	
	@Override
	public List<BarcodeImportDto> temp_listByImport(List<String> barCodes,String priceType,Double sp_rate,String sp_code,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT DISTINCT bc_pd_code,bc_subcode,bc_barcode,bc_size,pd_szg_code,bc_color,bc_bra,");
		if("1".equals(priceType)){//最近配送价
			sql.append(" f_GetProductSortPrice(pd_code,:sp_code,pd.companyid) AS unit_price,");
		}else if("2".equals(priceType)){//按照折扣率
			sql.append(" f_GetProductSellPrice(pd_code,:sp_code,pd.companyid)*:sp_rate AS unit_price,");
		}
		sql.append(" f_GetProductSellPrice(pd_code,:sp_code,pd.companyid) AS sell_price,");
		sql.append(" pd_cost_price AS cost_price");
		sql.append(" FROM t_base_barcode bc");
		sql.append(" JOIN t_base_product pd ON pd.pd_code = bc_pd_code AND pd.companyid = bc.companyid");
		sql.append(" WHERE 1=1");
		sql.append(" AND bc_barcode IN(:barcode)");
		sql.append(" AND bc.companyid = :companyid");
		return namedParameterJdbcTemplate.query(sql.toString(),
				new MapSqlParameterSource().addValue("barcode", barCodes)
						.addValue("sp_rate", sp_rate)
						.addValue("sp_code", sp_code)
						.addValue("companyid", companyid),
				new BeanPropertyRowMapper<>(BarcodeImportDto.class));
	}
	
	@Override
	public void detail_save(List<T_Sort_AllotList> details) {
		StringBuffer sql = new StringBuffer("");
		sql.append("INSERT INTO t_sort_allotlist");
		sql.append(" (atl_number,atl_pd_code,atl_sub_code,atl_sz_code,atl_szg_code,atl_cr_code,atl_br_code,atl_applyamount,atl_sendamount,atl_unitprice,atl_sellprice,atl_costprice,atl_remark,atl_type,companyid)");
		sql.append(" VALUES");
		sql.append(" (:atl_number,:atl_pd_code,:atl_sub_code,:atl_sz_code,:atl_szg_code,:atl_cr_code,:atl_br_code,:atl_applyamount,:atl_sendamount,:atl_unitprice,:atl_sellprice,:atl_costprice,:atl_remark,:atl_type,:companyid)");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(details.toArray()));
	}
	
	@Override
	public void detail_save(T_Sort_AllotList detail) {
		StringBuffer sql = new StringBuffer("");
		sql.append("INSERT INTO t_sort_allotlist");
		sql.append(" (atl_number,atl_pd_code,atl_sub_code,atl_sz_code,atl_szg_code,atl_cr_code,atl_br_code,atl_applyamount,atl_sendamount,atl_unitprice,atl_sellprice,atl_costprice,atl_remark,atl_type,companyid)");
		sql.append(" VALUES");
		sql.append(" (:atl_number,:atl_pd_code,:atl_sub_code,:atl_sz_code,:atl_szg_code,:atl_cr_code,:atl_br_code,:atl_applyamount,:atl_sendamount,:atl_unitprice,:atl_sellprice,:atl_costprice,:atl_remark,:atl_type,:companyid)");
		KeyHolder holder = new GeneratedKeyHolder(); 
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(detail),holder);
		detail.setAtl_id(holder.getKey().longValue());
	}
	
	@Override
	public void detail_update(List<T_Sort_AllotList> details) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_sort_allotlist");
		sql.append(" SET atl_sendamount = :atl_sendamount");
		sql.append(" WHERE 1=1");
		sql.append(" AND atl_sub_code = :atl_sub_code");
		sql.append(" AND atl_number = :atl_number");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(details.toArray()));
	}
	
	@Override
	public void detail_update(T_Sort_AllotList detail) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_sort_allotlist");
		sql.append(" SET atl_sendamount = :atl_sendamount");
		sql.append(" WHERE 1=1");
		sql.append(" AND atl_id = :atl_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(detail));
	}
	
	@Override
	public void detail_automatch(String number,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_sort_allotlist");
		sql.append(" SET atl_sendamount = atl_applyamount");
		sql.append(" WHERE 1=1");
		sql.append(" AND atl_applyamount <> 0");
		sql.append(" AND atl_number = :atl_number");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("atl_number", number).addValue("companyid", companyid));
	}
	
	@Override
	public void detail_del(List<T_Sort_AllotList> details) {
		StringBuffer sql = new StringBuffer("");
		sql.append("DELETE FROM t_sort_allotlist");
		sql.append(" WHERE 1=1");
		sql.append(" AND atl_sub_code = :atl_sub_code");
		sql.append(" AND atl_number = :atl_number");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(details.toArray()));
	}
	
	@Override
	public void detail_del(Integer atl_id) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" DELETE FROM t_sort_allotlist");
		sql.append(" WHERE atl_id=:atl_id");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("atl_id", atl_id));
	}
	
	@Override
	public void detail_updateprice(String pd_code, Double unitPrice, String at_number, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_sort_allotlist");
		sql.append(" SET atl_unitprice = :atl_unitprice");
		sql.append(" WHERE 1=1");
		sql.append(" AND atl_pd_code = :atl_pd_code");
		sql.append(" AND atl_number = :atl_number");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(),
				new MapSqlParameterSource()
						.addValue("atl_unitprice", unitPrice)
						.addValue("atl_pd_code", pd_code)
						.addValue("atl_number", at_number)
						.addValue("companyid", companyid));
	}
	
	@Override
	public void save(T_Sort_Allot allot,Integer atl_up, List<T_Sort_AllotList> details, T_Sys_User user) {
		String prefix = "";
		if(allot.getAt_type().equals(1)){//退货
			prefix = CommonUtil.NUMBER_PREFIX_SORT_ALLOT_PHTHD + DateUtil.getYearMonthDateYYMMDD();
		}else {
			if(CommonUtil.ONE.equals(user.getShoptype()) || (CommonUtil.TWO.equals(user.getShoptype()) && atl_up.intValue() == 0)){//配货发货单(总公司、分公司)
				prefix = CommonUtil.NUMBER_PREFIX_SORT_ALLOT_PHFHD + DateUtil.getYearMonthDateYYMMDD();
			}else {
				prefix = CommonUtil.NUMBER_PREFIX_SORT_ALLOT_PHSQD + DateUtil.getYearMonthDateYYMMDD();
			}
		}
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT CONCAT(:prefix,f_addnumber(MAX(at_number))) AS new_number");
		sql.append(" FROM t_sort_allot");
		sql.append(" WHERE 1=1");
		sql.append(" AND INSTR(at_number,:prefix) > 0");
		sql.append(" AND companyid = :companyid");
		String new_number = namedParameterJdbcTemplate.queryForObject(sql.toString(), 
				new MapSqlParameterSource().addValue("prefix", prefix).addValue("companyid", allot.getCompanyid()), String.class);
		allot.setAt_number(new_number);
		sql.setLength(0);
		sql.append("INSERT INTO t_sort_allot");
		sql.append(" (at_number,at_date,at_shop_code,at_outdp_code,at_indp_code,at_maker,at_manager,at_handnumber,at_applyamount,at_sendamount,");
		sql.append(" at_applymoney,at_sendmoney,at_sendcostmoney,at_sendsellmoney,at_discount_money,at_ba_code,at_property,at_remark,at_ar_state,at_ar_date,");
		sql.append(" at_pay_state,at_receivable,at_received,at_prepay,at_lastdebt,at_type,at_sysdate,at_us_id,at_isdraft,companyid)");
		sql.append(" VALUES");
		sql.append(" (:at_number,:at_date,:at_shop_code,:at_outdp_code,:at_indp_code,:at_maker,:at_manager,:at_handnumber,:at_applyamount,:at_sendamount,");
		sql.append(" :at_applymoney,:at_sendmoney,:at_sendcostmoney,:at_sendsellmoney,:at_discount_money,:at_ba_code,:at_property,:at_remark,:at_ar_state,:at_ar_date,");
		sql.append(" :at_pay_state,:at_receivable,:at_received,:at_prepay,:at_lastdebt,:at_type,:at_sysdate,:at_us_id,:at_isdraft,:companyid)");
		KeyHolder holder = new GeneratedKeyHolder(); 
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(allot),holder);
		allot.setAt_id(holder.getKey().intValue());
		for(T_Sort_AllotList item:details){
			item.setAtl_number(allot.getAt_number());
		}
		sql.setLength(0);
		sql.append("INSERT INTO t_sort_allotlist");
		sql.append(" (atl_number,atl_pd_code,atl_sub_code,atl_sz_code,atl_szg_code,atl_cr_code,atl_br_code,atl_applyamount,atl_sendamount,atl_unitprice,atl_sellprice,atl_costprice,atl_remark,atl_type,companyid)");
		sql.append(" VALUES");
		sql.append(" (:atl_number,:atl_pd_code,:atl_sub_code,:atl_sz_code,:atl_szg_code,:atl_cr_code,:atl_br_code,:atl_applyamount,:atl_sendamount,:atl_unitprice,:atl_sellprice,:atl_costprice,:atl_remark,:atl_type,:companyid)");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(details.toArray()));
	}
	
	@Override
	public void update(T_Sort_Allot allot, List<T_Sort_AllotList> details) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" UPDATE t_sort_allot");
		sql.append(" SET at_date=:at_date");
		sql.append(" ,at_shop_code=:at_shop_code");
		sql.append(" ,at_outdp_code=:at_outdp_code");
		sql.append(" ,at_indp_code=:at_indp_code");
		sql.append(" ,at_maker=:at_maker");
		sql.append(" ,at_manager=:at_manager");
		sql.append(" ,at_handnumber=:at_handnumber");
		sql.append(" ,at_applyamount=:at_applyamount");
		sql.append(" ,at_sendamount=:at_sendamount");
		sql.append(" ,at_applymoney=:at_applymoney");
		sql.append(" ,at_sendmoney=:at_sendmoney");
		sql.append(" ,at_sendcostmoney=:at_sendcostmoney");
		sql.append(" ,at_sendsellmoney=:at_sendsellmoney");
		sql.append(" ,at_discount_money=:at_discount_money");
		sql.append(" ,at_ba_code=:at_ba_code");
		sql.append(" ,at_property=:at_property");
		sql.append(" ,at_remark=:at_remark");
		sql.append(" ,at_ar_state=:at_ar_state");
		sql.append(" ,at_ar_date=:at_ar_date");
		sql.append(" ,at_receivable=:at_receivable");
		sql.append(" ,at_us_id=:at_us_id");
		sql.append(" WHERE at_id=:at_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(allot));
		for(T_Sort_AllotList item:details){
			item.setAtl_number(allot.getAt_number());
		}
		sql.setLength(0);
		sql.append("INSERT INTO t_sort_allotlist");
		sql.append(" (atl_number,atl_pd_code,atl_sub_code,atl_sz_code,atl_szg_code,atl_cr_code,atl_br_code,atl_applyamount,atl_sendamount,atl_unitprice,atl_sellprice,atl_costprice,atl_remark,atl_type,companyid)");
		sql.append(" VALUES");
		sql.append(" (:atl_number,:atl_pd_code,:atl_sub_code,:atl_sz_code,:atl_szg_code,:atl_cr_code,:atl_br_code,:atl_applyamount,:atl_sendamount,:atl_unitprice,:atl_sellprice,:atl_costprice,:atl_remark,:atl_type,:companyid)");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(details.toArray()));
	}
	
	@Override
	public void updateApprove(T_Sort_Allot allot) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" UPDATE t_sort_allot");
		sql.append(" SET at_ar_state=:at_ar_state");
		sql.append(" ,at_ar_date = :at_ar_date");
		sql.append(" WHERE at_id=:at_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(allot));
	}
	
	@Override
	public void updateSend(T_Sort_Allot allot) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT SUM(atl_sendamount) AS at_sendamount,");
		sql.append(" SUM(atl_sendamount*atl_unitprice) AS at_sendmoney,");
		sql.append(" SUM(atl_sendamount*atl_costprice) AS at_sendcostmoney,");
		sql.append(" SUM(atl_sendamount*atl_sellprice) AS at_sendsellmoney");
		sql.append(" FROM t_sort_allotlist");
		sql.append(" WHERE 1=1");
		sql.append(" AND atl_number = :at_number");
		sql.append(" AND companyid = :companyid");
		T_Sort_Allot statAllot = namedParameterJdbcTemplate.queryForObject(sql.toString(), new BeanPropertySqlParameterSource(allot),
				new BeanPropertyRowMapper<>(T_Sort_Allot.class));
		allot.setAt_sendamount(statAllot.getAt_sendamount());
		allot.setAt_sendmoney(statAllot.getAt_sendmoney());
		allot.setAt_sendcostmoney(statAllot.getAt_sendcostmoney());
		allot.setAt_sendsellmoney(statAllot.getAt_sendsellmoney());
		allot.setAt_receivable(allot.getAt_sendmoney()-allot.getAt_discount_money());
		sql.setLength(0);
		sql.append(" UPDATE t_sort_allot");
		sql.append(" SET at_ar_state=:at_ar_state");
		sql.append(" ,at_outdp_code = :at_outdp_code");
		sql.append(" ,at_sendamount = :at_sendamount");
		sql.append(" ,at_sendmoney = :at_sendmoney");
		sql.append(" ,at_sendcostmoney = :at_sendcostmoney");
		sql.append(" ,at_sendsellmoney = :at_sendsellmoney");
		sql.append(" ,at_receivable = :at_receivable");
		sql.append(" WHERE at_id=:at_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(allot));
	}
	
	@Override
	public void updateReceive(T_Sort_Allot allot) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" UPDATE t_sort_allot");
		sql.append(" SET at_ar_state=:at_ar_state");
		sql.append(" ,at_indp_code = :at_indp_code");
		sql.append(" WHERE at_id=:at_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(allot));
	}
	
	@Override
	public List<ProductStorePriceDto> load_allot_product_price(String at_number,String shop_code,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT pdp_id,pdp_cost_price,pdp_vip_price,pdp_sell_price,pdp_sort_price,");
		sql.append(" pd_code,pd_sell_price,pd_vip_price,pd_sort_price,pd_cost_price,atl_unitprice AS unitprice,SUM(atl_sendamount) AS amount");
		sql.append(" FROM t_sort_allotlist t");
		sql.append(" JOIN t_base_product pd ON atl_pd_code = pd_code AND t.companyid = pd.companyid");
		sql.append(" LEFT JOIN t_base_product_shop_price pdp ON pdp_pd_code = pd_code AND pd.companyid = pdp.companyid AND pdp_shop_code = :shop_code");
		sql.append(" WHERE 1=1");
		sql.append(" AND atl_number = :atl_number");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" GROUP BY atl_pd_code");
		return namedParameterJdbcTemplate.query(sql.toString(),
				new MapSqlParameterSource().addValue("atl_number", at_number).addValue("shop_code", shop_code).addValue("companyid", companyid),
				new BeanPropertyRowMapper<>(ProductStorePriceDto.class));
	}
	
	@Override
	public void update_base_shopprice(List<T_Base_Product_Shop_Price> shopPrices_add,List<T_Base_Product_Shop_Price> shopPrices_update) {
		//更新
		StringBuffer sql = new StringBuffer("");
		if (shopPrices_update.size() > 0) {
			sql.append(" UPDATE t_base_product_shop_price");
			sql.append(" SET pdp_sort_price = :pdp_sort_price");
			sql.append(" ,pdp_cost_price = :pdp_cost_price");
			sql.append(" WHERE pdp_id = :pdp_id");
			namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(shopPrices_update.toArray()));
		}
		//保存
		if (shopPrices_add.size() > 0) {
			sql.setLength(0);
			sql.append(" INSERT INTO t_base_product_shop_price(pdp_pd_code,pdp_shop_code,pdp_cost_price,pdp_sell_price,pdp_vip_price,pdp_sort_price,companyid)");
			sql.append(" VALUES(:pdp_pd_code,:pdp_shop_code,:pdp_cost_price,:pdp_sell_price,:pdp_vip_price,:pdp_sort_price,:companyid)");
			namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(shopPrices_add.toArray()));
		}
	}
	
	@Override
	public List<T_Stock_DataBill> listStockAmount(String number,String shop_code, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT atl_pd_code AS sd_pd_code,SUM(ABS(sd_amount)) AS sd_amount");
		sql.append(" FROM t_sort_allotlist atl");
		sql.append(" JOIN t_stock_data sd ON atl_sub_code = sd_code AND atl.companyid = sd.companyid");
		sql.append(" WHERE 1=1");
		sql.append(" AND sd_dp_code IN (SELECT dp_code FROM t_base_depot dp WHERE dp_shop_code = :shop_code AND dp.companyid = :companyid)");
		sql.append(" AND atl_number = :atl_number");
		sql.append(" AND atl.companyid = :companyid");
		sql.append(" GROUP BY atl_pd_code");
		return namedParameterJdbcTemplate.query(sql.toString(),
				new MapSqlParameterSource().addValue("atl_number", number).addValue("shop_code", shop_code).addValue("companyid", companyid),
				new BeanPropertyRowMapper<>(T_Stock_DataBill.class));
	}
	
	@Override
	public void updateReject(T_Sort_Allot allot) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" UPDATE t_sort_allot");
		sql.append(" SET at_ar_state=:at_ar_state");
		sql.append(" WHERE at_id=:at_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(allot));
	}
	
	@Override
	public void updateRejectConfirm(T_Sort_Allot allot) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" UPDATE t_sort_allot");
		sql.append(" SET at_ar_state=:at_ar_state");
		sql.append(" ,at_sendamount=:at_sendamount");
		sql.append(" ,at_sendmoney=:at_sendmoney");
		sql.append(" ,at_sendcostmoney=:at_sendcostmoney");
		sql.append(" ,at_sendsellmoney=:at_sendsellmoney");
		sql.append(" ,at_receivable=:at_receivable");
		sql.append(" WHERE at_id=:at_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(allot));
	}
	
	@Override
	public List<T_Stock_DataBill> listStock(String number,String dp_code, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT atl_pd_code AS sd_pd_code,atl_sub_code AS sd_code,atl_cr_code AS sd_cr_code,atl_sz_code AS sd_sz_code,");
		sql.append(" atl_br_code AS sd_br_code,SUM(ABS(atl_sendamount)) AS bill_amount,sd_id,sd_amount");
		sql.append(" FROM t_sort_allotlist atl");
		sql.append(" LEFT JOIN t_stock_data sd ON atl_sub_code = sd_code AND atl.companyid = sd.companyid AND sd.sd_dp_code = :dp_code");
		sql.append(" WHERE 1=1");
		sql.append(" AND atl_number = :atl_number");
		sql.append(" AND atl.companyid = :companyid");
		sql.append(" GROUP BY atl_sub_code");
		return namedParameterJdbcTemplate.query(sql.toString(),
				new MapSqlParameterSource().addValue("atl_number", number).addValue("dp_code", dp_code).addValue("companyid", companyid),
				new BeanPropertyRowMapper<>(T_Stock_DataBill.class));
	}
	
	@Override
	public void del(String at_number, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" DELETE FROM t_sort_allot");
		sql.append(" WHERE at_number=:at_number");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("at_number", at_number).addValue("companyid", companyid));
		sql.setLength(0);
		sql.append(" DELETE FROM t_sort_allotlist");
		sql.append(" WHERE atl_number=:atl_number");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("atl_number", at_number).addValue("companyid", companyid));
	}
	
	@Override
	public void deleteList(String at_number, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" DELETE FROM t_sort_allotlist");
		sql.append(" WHERE atl_number=:atl_number");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("atl_number", at_number).addValue("companyid", companyid));
	}
	
}
